package com.hhz.mp;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.hhz.mp.enums.SexEnum;
import com.hhz.mp.mapper.UserMapper;
import com.hhz.mp.pojo.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * mp wrapper 条件构造器
 *
 * @Author Rem
 * @Date 2020-02-28
 */

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class MyApplicationTest3 {

    @Autowired
    private UserMapper userMapper;


    /**
     * allEq(Map<R, V> params) 根据map给定字段的条件查询
     * SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (password IS NULL AND user_name = ? AND age = ?)
     */
    @Test
    public void testllEq() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        Map<String, Object> map = new HashMap<>();
        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);

        wrapper.allEq(map);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

    /**
     * allEq(Map<R, V> params, boolean null2IsNull) 根据map给定字段的条件查询  过滤字段为空的数据
     * SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (user_name = ? AND age = ?)
     */
    @Test
    public void testAllEq2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        Map<String, Object> map = new HashMap<>();
        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);

        wrapper.allEq(map, false);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }

    /**
     * allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 字段过滤接口，传入多参数时允许对参数进行过滤
     * SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (age = ?)   user_name被过滤   password为空也被过滤
     */

    @Test
    public void testAllEq3() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        Map<String, Object> map = new HashMap<>();

        map.put("age", 24);
        map.put("user_name", "sunqi");
        map.put("password", null);


        wrapper.allEq((k, v) -> k.equals("age") || k.equals("name") || k.equals("password"), map, false);
        List<User> users = userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }


    /**
     * eq 等于
     * ge 大于等于
     * in 在范围内
     * gt 大于
     * lt 小于
     * le 小于等于
     * between  两个之间
     * isNotNULL 非空
     */
    @Test
    public void testEq() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (password = ? AND age >= ? AND name IN (?, ?, ?))
        wrapper.eq("password", "123456")
                .ge("age", 20)
                .in("name", "李四", "王五", "赵六");
        List<User> users = this.userMapper.selectList(wrapper);
        users.forEach(System.err::println);
    }


    /**
     * 模糊查询
     * like(R column, Object val)                           LIKE '%值%'
     * like(boolean condition, R column, Object val)
     * likeLeft(boolean condition, R column, Object val)    LIKE '%值'
     * likeRight(boolean condition, R column, Object val)   LIKE '值%'
     */
    @Test
    public void testLike() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        // wrapper.like("name", "孙");

        User user = new User();
        user.setName("孙");

        //condition  条件判断  如果为false 该条就会被忽略
        wrapper.like(StringUtils.isNotBlank(user.getName()), "name", "孙");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }


    /**
     * 排序
     * orderByAsc
     * orderByDesc
     */
    @Test
    public void testOrderBy() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.orderByDesc("age", "user_name");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }


    /**
     * Or  逻辑关联
     * 查找姓名为孙权或者年龄为20的人
     * SELECT id, email AS mail, name, user_name, age FROM tb_user WHERE (name = ? OR age = ?)
     */
    @Test
    public void testOr() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.eq("name", "孙权").or().eq("age", "20");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * or(Function<Param, Param> func)  逻辑关联
     * 查找姓氏为李 或者  年龄28到30的女性
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND name LIKE ? OR (age BETWEEN ? AND ? AND sex = ?)
     */
    @Test
    public void testOr2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.likeRight("name", "李").or(wrap -> wrap.between("age", 28, 30).eq("sex", SexEnum.WOMAN));

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }


    /**
     * select 选择需要的字段
     * SELECT name, age FROM tb_user
     */
    @Test
    public void testSelect() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.select("name", "age");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * Select 排除字段  deleted   version  email
     * SELECT id, user_name, password, name, age, sex FROM tb_user WHERE deleted = 0
     */
    @Test
    public void testSelect2() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //先按年龄倒序排序  年龄一致按照user_name
        wrapper.select(User.class, t -> !t.getColumn().equals("deleted") &&
                !t.getColumn().equals("version") &&
                !t.getColumn().equals("email")
        );

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }


    /**
     * apply
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age BETWEEN ? AND ?
     * <p>
     * apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
     */
    @Test
    public void testApply() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        //拼接sql，   可以直接在applySql中输入值，但是有注入风险 ，用{}方式会更好
        //wrapper.between("age", 30, 42);
        //wrapper.apply("age between 30 and 42");
        wrapper.apply("age between {0} and {1}", 30, 42);

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * in
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND age IN (?, ?, ?)
     */
    @Test
    public void testIn() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.in("age", 20, 30, 40);

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * inSql  子查询
     * 查询性别为女性并且和年龄和孙姓的有一样的
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND sex = ? AND age IN (SELECT age FROM tb_user WHERE name LIKE '孙%')
     */
    @Test
    public void testInSQL() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("sex", SexEnum.WOMAN).inSql("age", "select age from tb_user where name like '孙%'");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }


    /**
     * having
     */
    @Test
    public void testHaving() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.groupBy("id").having("age > {0}", 30);

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);

    }


    /**
     * nested(Function<Param, Param> func) 嵌套语句
     * 查询 姓名为李开头或者是女性  或  密码是888888的人
     * SELECT id, user_name, nme, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND (name LIKE ? OR (sex <> ?)) OR password = ?
     */
    @Test
    public void testNested() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.nested(wr -> wr.likeRight("name", "李").or(w -> w.ne("sex", SexEnum.MAN))).or().eq("password", "888888");

        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * last  在最后拼接语句
     * SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 LIMIT 2
     */
    @Test
    public void testLast() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.last("limit 2");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);
    }

    /**
     * exists
     * 拼接exists语句 子查询 返回true或者false 作为条件应用到外层sql
     */
    @Test
    public void testExists() {
        //SELECT id, user_name, name, age, email AS mail, version, deleted, sex FROM tb_user WHERE deleted = 0 AND EXISTS (SELECT * FROM tb_user WHERE age > 60)
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.exists("select * from tb_user where age >60");
        List<User> userList = userMapper.selectList(wrapper);
        userList.forEach(System.err::println);

    }

}